
/**
	AUTHOR		: VEERU
	DATE		: FEB 10th 2004
	DESCRIPTION	: THIS FUNCTION IS USED TO FETCH ALL THE AVAILABLE PROJECT SETUPS.
*/

ALTER FUNCTION FN_LOAD_PROJECTS()
RETURNS @FN_RESULTS TABLE ( PROJECTID INT, PROJECT VARCHAR(100), PARTS VARCHAR(3500), CUSTOMER VARCHAR(50), ODM VARCHAR(50), SALESPERSON VARCHAR(50), MKTG_PARTS VARCHAR(3500), ENDUSERNAME VARCHAR(50) )


AS
BEGIN
	DECLARE @RESULTS TABLE ( PROJECTID INT, PROJECT VARCHAR(100), PARTS VARCHAR(3500), CUSTOMER VARCHAR(50), ODM VARCHAR(50), SALESPERSON VARCHAR(50), MKTG_PARTS VARCHAR(3500), ENDUSERNAME VARCHAR(50) )
	DECLARE @PROJID     INT 
	DECLARE @PARTID     INT	
	DECLARE @PROJECT    VARCHAR(100) 
	DECLARE @PARTS      VARCHAR(3500) 
	DECLARE @PART       VARCHAR(50)
	DECLARE @CUSTOMER   VARCHAR(50)
	DECLARE @ODM	    VARCHAR(50)
	DECLARE @SALESPERSON VARCHAR(50)
    DECLARE @ENDUSERNAME VARCHAR(50)

	
	DECLARE @PROJECTCURSOR CURSOR 
	DECLARE @PARTSCURSOR   CURSOR 
	
	DECLARE @MKTG_PARTS VARCHAR(3500)  --ADDED BY SUJAN ON 31st MARCH 2005 TO GET THE MARKETING PARTS FOR THE PROJECT

	SET @PROJECTCURSOR = CURSOR FOR SELECT PROJID, PROJNAME, C_NAME, 
					CASE 	
					WHEN ODMID IS NULL OR ODMID = 0 THEN ''
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = ODMID )
					END AS ODM, SALESPERSON_NAME,
                    CASE
					WHEN ENDUSERID IS NULL OR ENDUSERID = 0 THEN ''
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = ENDUSERID ) END AS ENDUSERNAME
					FROM CUSTOMER C, PROJSETUP P, SALES_PERSONS SP
					WHERE SP.SALESPERSON_ID = P.SPERSONID AND CUSTOMER_ID = CUSTID AND C_DISABLED = 1 AND PROJDISABLED = 1
	OPEN @PROJECTCURSOR
	FETCH NEXT FROM @PROJECTCURSOR INTO  @PROJID, @PROJECT, @CUSTOMER, @ODM, @SALESPERSON, @ENDUSERNAME
	WHILE @@FETCH_STATUS = 0
	BEGIN		
		SET @PARTSCURSOR = CURSOR FOR SELECT PRODID FROM PROJPRODREL WHERE PROJID = @PROJID			
		SET @PARTS = ''
		SET @MKTG_PARTS = ''
		OPEN @PARTSCURSOR
		FETCH NEXT FROM @PARTSCURSOR INTO  @PARTID
		WHILE @@FETCH_STATUS = 0
		BEGIN		
			IF ( @PARTS <> '')
			BEGIN			
				SET @PARTS = @PARTS +','+ ( SELECT  LTRIM(RTRIM( PART_NUMBER ) ) FROM PRODUCT WHERE PRODUCT_ID = @PARTID )
				SET @MKTG_PARTS = @MKTG_PARTS +','+ ( SELECT  LTRIM(RTRIM( P_MARKETING_PART_NUMBER ) ) FROM PRODUCT WHERE PRODUCT_ID = @PARTID )
			END
			ELSE
			BEGIN
				SET @PARTS = @PARTS + ( SELECT  LTRIM(RTRIM( PART_NUMBER ) ) FROM PRODUCT WHERE PRODUCT_ID = @PARTID )
				SET @MKTG_PARTS = @MKTG_PARTS + ( SELECT  LTRIM(RTRIM( P_MARKETING_PART_NUMBER ) ) FROM PRODUCT WHERE PRODUCT_ID = @PARTID )
			END
			SET @PART = NULL
			FETCH NEXT FROM @PARTSCURSOR INTO  @PARTID
		END	
		CLOSE @PARTSCURSOR 
		DEALLOCATE @PARTSCURSOR 
		
		IF LEN( @PARTS ) > 3499 
		BEGIN
			SET @PARTS = SUBSTRING( @PARTS, 0, 3496 ) + '.....'
			
		END
		IF LEN( @MKTG_PARTS ) > 3499 
		BEGIN
			SET @MKTG_PARTS = SUBSTRING( @MKTG_PARTS, 0, 3496 ) +'.....' 
		END
  		INSERT INTO @RESULTS( PROJECTID, PROJECT, PARTS, CUSTOMER, ODM, SALESPERSON, MKTG_PARTS, ENDUSERNAME ) VALUES( @PROJID, @PROJECT, @PARTS, @CUSTOMER, @ODM, @SALESPERSON, @MKTG_PARTS, @ENDUSERNAME )
		SET @PARTS = ''
		SET @MKTG_PARTS = ''	
		SET @PROJID = NULL
		SET @PROJECT = NULL
		SET @CUSTOMER = NULL	
		SET @ODM = NULL
		SET @SALESPERSON = NULL
		FETCH NEXT FROM @PROJECTCURSOR INTO  @PROJID, @PROJECT, @CUSTOMER, @ODM, @SALESPERSON, @ENDUSERNAME
	END
	CLOSE @PROJECTCURSOR 
	DEALLOCATE @PROJECTCURSOR 

	INSERT INTO @FN_RESULTS SELECT * FROM @RESULTS ORDER BY PROJECT
	RETURN

END
